package cn.com.headfree.workflowant.dao.impl;

import cn.com.headfree.entity.TBusinessWorkflowBind;
import cn.com.headfree.entity.TMouldInfo;
import cn.com.headfree.entity.TOrgDept;
import cn.com.headfree.entity.TSysDictionaryInfo;
import cn.com.headfree.entity.isc.User;
import cn.com.headfree.util.DateUtils;
import cn.com.headfree.vo.workflow.ProcessListVo;
import cn.com.headfree.vo.workflow.TBusinessWorkflowBindVO;
import cn.com.headfree.workflowant.dao.IWorkFlowAntDao;
import cn.com.headfree.workflowant.dto.ApproveWorkFlowDto;
import cn.com.headfree.workflowant.dto.BusinessWorkFlowDto;
import cn.com.headfree.workflowant.dto.WorkFlowCount;
import com.gta.edu.sdk.base.dao.impl.BaseDaoImpl;
import com.gta.edu.sdk.common.dto.Page;
import io.micrometer.core.instrument.util.StringUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import java.util.*;

/**
 * @Title  WorkFlowAntDaoImpl.java
 * @Package cn.com.headfree.workflowant.dao.impl
 * @Description 流程daoImpl
 * @author yangwang
 * @date  2020年7月29日 下午5:38:27
 */
@Repository("workFlowAntDao")
public class WorkFlowAntDaoImpl extends BaseDaoImpl<ApproveWorkFlowDto> implements IWorkFlowAntDao {

	public static String waitStatus = " = ";
	public static String downStatus = " != ";

	@Value("${spring.profiles.active}")
	private String spring_profiles_active;

	/**
	 * 获取某个单位下的所有单位
	 * @author yangwang
	 * @return  List<TOrgDept>
	 *  */
	@Override
	public List<TOrgDept> getWorkFlowUnits(){
		String sql = "select distinct id,name,p_id";
		StringBuilder sb = new StringBuilder(sql);

		sb.append(" from t_org_dept")
		.append(" where is_audit_unit=0")
		.append(" order by sort asc");

		return this.findAllBySQL(TOrgDept.class, sb.toString(), null, false, new Object[] {});
	}

	/**
	 * 获取所有业务类型
	 * @author yangwang
	 * @param ids
	 * @return  List<TMouldInfo>
	 *  */
	@Override
	public List<TMouldInfo> getMouldInfoList(String ids){
		String sql = "select distinct ID,MOULD_NAME,TYPE,VALUE,SORT,EASY_NAME,TAB_NAME";
		StringBuilder sb = new StringBuilder(sql);

		sb.append(" from t_mould_info")
		.append(" where DEL_SIGN=0")
		.append(" and EASY_NAME <> 'jbxx'")
		.append(" and TAB_NAME is not null");

		List<Object> paramList = new ArrayList<Object>();
		if(StringUtils.isNotBlank(ids)) {
			String[] idArr = ids.split(",|;|，|；");
			if((null != idArr)&&(0 < idArr.length)) {
				sb.append(" and (");
				Boolean bool = false;
				for(int i=0;i<idArr.length;i++) {
					if(StringUtils.isNotBlank(idArr[i])) {
						if(bool) {
							sb.append(" or ");
						}
						sb.append(" id=?");
						bool = true;
						paramList.add(idArr[i]);
					}
				}
				sb.append(")");
			}
		}

		sb.append(" order by sort asc");

		return this.findAllBySQL(TMouldInfo.class, sb.toString(), null, false, paramList.toArray());
	}

	/**
	 * 根据id获取业务类型
	 * @author yangwang
	 * @param id
	 * @return  TMouldInfo
	 *  */
	@Override
	public TMouldInfo getMouldInfoById(String id) {
		String sql = "select distinct ID,MOULD_NAME,TYPE,VALUE,SORT,EASY_NAME,TAB_NAME";
		StringBuilder sb = new StringBuilder(sql);

		sb.append(" from t_mould_info")
		.append(" where DEL_SIGN=0")
		.append(" and EASY_NAME <> 'jbxx'")
		.append(" and id=?");

		return this.findUnique(TMouldInfo.class, sb.toString(), new Object[] {id});
	}

	/**
	 * 根据单位和流程获取流程绑定信息
	 * @author yangwang
	 * @param orgId 单位id
	 * @param flowId 流程id
	 * @return  TBusinessWorkflowBind
	 *  */
	@Override
	public List<TBusinessWorkflowBind> getWorkflowBindByOrgAndFlowId(String orgId,String flowId) {
		String sql = "select distinct id,org_id,business_type_id,flow_id";
		StringBuilder sb = new StringBuilder(sql);

		sb.append(" from t_business_workflow_bind")
		.append(" where org_id=?")
		.append(" and flow_id=?");

		return this.findAllBySQL(TBusinessWorkflowBind.class, sb.toString(), null, false, new Object[] {orgId,flowId});
	}

	/**
	 * 获取流程绑定
	 * @author yangwang
	 * @param bind 绑定信息
	 * @return TBusinessWorkflowBind
	 *  */
	@Override
	public TBusinessWorkflowBind getWorkFlowBind(TBusinessWorkflowBind bind) {
		String sql = "select distinct id,org_id,business_type_id,flow_id";
		StringBuilder sb = new StringBuilder(sql);

		sb.append(" from t_business_workflow_bind")
		.append(" where org_id=?")
		.append(" and business_type_id=?");

		return this.findUnique(TBusinessWorkflowBind.class, sb.toString(), new Object[] {bind.getOrgId(),bind.getBusinessTypeId()});
	}

	/**
	 * 插入流程绑定
	 * @author yangwang
	 * @param bind 绑定信息
	 *  */
	@Override
	public void insertWorkFlowBind(TBusinessWorkflowBind bind) {
		String sql = "insert into t_business_workflow_bind(id,org_id,business_type_id,flow_id) values";
		StringBuilder sb = new StringBuilder(sql);
		sb.append("(#{id,jdbcType=VARCHAR},")
		.append("#{org_id,jdbcType=VARCHAR},")
		.append("#{business_type_id,jdbcType=VARCHAR},")
		.append("#{flow_id,jdbcType=VARCHAR})");

		Map<String, Object> params = new HashMap<String, Object>();
        params.put("id", bind.getId());
        params.put("org_id", bind.getOrgId());
        params.put("business_type_id", bind.getBusinessTypeId());
        params.put("flow_id", bind.getFlowId());

        this.executeSql(sb.toString(), params);
	}

	/**
	 * 更新流程绑定
	 * @author yangwang
	 * @param bind 绑定信息
	 *  */
	@Override
	public void updateWorkFlowBind(TBusinessWorkflowBind bind) {
		String sql = "UPDATE t_business_workflow_bind SET ";
		StringBuilder sb = new StringBuilder(sql);

		sb.append(" org_id=#{org_id,jdbcType=VARCHAR},")
		.append(" business_type_id=#{business_type_id,jdbcType=VARCHAR},")
		.append(" flow_id=#{flow_id,jdbcType=VARCHAR}");
		sb.append(" where id=#{id,jdbcType=VARCHAR} ");

		Map<String, Object> params = new HashMap<String, Object>();
		params.put("org_id", bind.getOrgId());
		params.put("business_type_id", bind.getBusinessTypeId());
		params.put("flow_id", bind.getFlowId());
		params.put("id", bind.getId());

		this.executeSql(sb.toString(), params);
	}

	/**
	 * 根据erpno获取部门信息
	 * @author yangwang
	 * @param erpNo erp编码
	 *  */
	@Override
	public List<TOrgDept> getDeptByErpNo(String erpNo){
		String sql = "select tod.id,tod.name";
		StringBuilder sb = new StringBuilder(sql);
		sb.append(" from t_dept_emp_rela tder")
		.append(" left join t_org_dept tod on tder.dept_id=tod.id")
		.append(" where tder.erp_no=?");

		return this.findAllBySQL(TOrgDept.class, sb.toString(), null, false, new Object[] {erpNo});
	}

	/**
	 * 根据表名和业务id获取业务相关信息
	 * @author yangwang
	 * @param tableName 表名
	 * @param businessId 业务id
	 *  */
	@Override
	public ApproveWorkFlowDto getBusinessDataByParams(String tableName,String businessId) {
		String sql = "select distinct ID as BUSINESS_ID,CREATE_TIME as DEAL_TIME,creater_id as erp_no,creater_name as user_name,update_time as HANDLE_TIME,flow_status,primitive_id,orignal_id";
		StringBuilder sb = new StringBuilder(sql);

		sb.append(" from ")
		.append(tableName)
		.append(" where 1=1")
//		.append(" and DEL_SIGN=0")
		.append(" and ID=?");

		return this.findUnique(ApproveWorkFlowDto.class, sb.toString(), new Object[] {businessId});
	}

	/**
	 * 获取审核进度列表数据
	 * @author yangwang
	 * @param page 分页
	 * @param process 进度数据
	 *  */
	@Override
	public void approveProcessPage(Page<ProcessListVo> page,ProcessListVo process) {
		String sql = "select distinct t.id as business_id,t.flow_status,GET_DIC('FlowAuditStatus',t.flow_status) as flow_status_name,t.create_time,t.update_time,t.update_time as deal_time,t.primitive_id";
		StringBuilder sb = new StringBuilder(sql);
		String[] tableNames = new String[] {"t_acade_achive_info","t_patent_info","t_proj_achieve_info","t_stand_info","t_tec_innov_info","t_tec_man_innov","t_tec_standard_info"};

		sb.append(" from ")
		.append(" (SELECT * FROM (")
		.append(" SELECT distinct id,primitive_id,create_time,update_time")
		.append(" FROM ")
		.append(process.getTabName())
		.append(" ORDER BY create_time DESC,update_time DESC) m")
		.append(" GROUP BY m.primitive_id")
		.append(" ORDER BY m.create_time DESC,m.update_time DESC) temp")
		.append(" LEFT JOIN ")
		.append(process.getTabName())
		.append("  t ON t.id=temp.id");

		if(Arrays.asList(tableNames).contains(process.getTabName().toLowerCase())) {//判断是否多表
			sb.append(" left join t_part_rank pr on t.id=pr.item_id");
		}

		sb.append(" where 1=1")
		.append(" and t.DEL_SIGN=0");

		List<Object> paramList = new ArrayList<Object>();
		if(null != process) {
			if((null != process.getFlowStatus())&&(-1 != process.getFlowStatus())){
	        	sb.append(" and t.flow_status = ? ");
	        	paramList.add(process.getFlowStatus());
	        }
	        if(StringUtils.isNotBlank(process.getDealErpNo())){
	        	if(Arrays.asList(tableNames).contains(process.getTabName().toLowerCase())) {//判断是否多表
	        		sb.append(" and pr.erp_no = ? ");//进度查看只能查看自己作为参与人的
					sb.append(" and t.creater_id = ? ");//且自己是创建人的
					paramList.add(process.getDealErpNo());
				}else {//非多表
					sb.append(" and t.erp_no = ? ");
				}
	        	paramList.add(process.getDealErpNo());
	        }
	        if(null != process.getStartTime()){
	        	sb.append(" and t.create_time >= ? ");
	        	paramList.add(DateUtils.getStartTimeOfDay(process.getStartTime()));
	        }
	        if(null != process.getEndTime()){
	        	sb.append(" and t.create_time <= ? ");
	        	paramList.add(DateUtils.getEndTimeOfDay(process.getEndTime()));
	        }
		}
		sb.append(" order by t.create_time desc ");

        this.findPageBySql(ProcessListVo.class, sb.toString(), page, paramList.toArray());
	}


	/**
	 * 更新业务流程状态
	 * @author yangwang
	 * @param businessDto
	 *  */
	@Override
	public void updateBusinessWorkFlowStatus(BusinessWorkFlowDto businessDto) {
		String sql = "UPDATE ";
		StringBuilder sb = new StringBuilder(sql);

		sb.append(businessDto.getTabName())
		.append(" set ")
		.append(" flow_status=#{flow_status,jdbcType=INTEGER},")
		.append(" updater_id=#{updater_id,jdbcType=VARCHAR},")
		.append(" updater_name=#{updater_name,jdbcType=VARCHAR},")
		.append(" update_time=#{update_time}");
		if((null != businessDto)&&(1 == businessDto.getFolwStatus())) {
			sb.append(",is_valid=0");
		}
		sb.append(" where id=#{id,jdbcType=VARCHAR} ");

		Map<String, Object> params = new HashMap<String, Object>();
		params.put("flow_status", businessDto.getFolwStatus());
		params.put("updater_id", businessDto.getErpNo());
		params.put("updater_name", businessDto.getUserName());
		params.put("update_time", businessDto.getDealTime());
		params.put("id", businessDto.getBusinessId());

		this.executeSql(sb.toString(), params);
	}

	/**
	 * 更新业务是否有效数据
	 * @author yangwang
	 * @param businessDto
	 *  */
	@Override
	public void updateBusinessIsValid(BusinessWorkFlowDto businessDto) {
		if((null != businessDto)&&(1 == businessDto.getFolwStatus())) {
			String sql = "UPDATE ";
			StringBuilder sb = new StringBuilder(sql);

			sb.append(businessDto.getTabName())
			.append(" set ")
			.append(" is_valid=1,")
			.append(" is_cover=#{is_cover,jdbcType=INTEGER},")
			.append(" updater_id=#{updater_id,jdbcType=VARCHAR},")
			.append(" updater_name=#{updater_name,jdbcType=VARCHAR},")
			.append(" update_time=#{update_time}")
			.append(" where id=(")
			.append(" select temp.orignal_id")
			.append(" from (select orignal_id")
			.append(" from ")
			.append(businessDto.getTabName())
			.append(" where id=#{id,jdbcType=VARCHAR}) temp)");

			Map<String, Object> params = new HashMap<String, Object>();
			params.put("is_cover", businessDto.getIsCover());//is_cover = 1 表明该条数据因为它的更新数据被审核通过，因此这条数据被覆盖，并设置is_valid=1无效
			params.put("updater_id", businessDto.getErpNo());
			params.put("updater_name", businessDto.getUserName());
			params.put("update_time", businessDto.getDealTime());
			params.put("id", businessDto.getBusinessId());

			this.executeSql(sb.toString(), params);
		}
	}

	/**
	 * 获取业务流程统计
	 * @author yangwang
	 * @param tableName 表名
	 *  */
	@Override
	public WorkFlowCount getBusinessFlowCount(String tableName,ProcessListVo process) {
		String sql = "select COUNT(t.id) as flow_count";
		StringBuilder sb = new StringBuilder(sql);
		List<Object> paramList = new ArrayList<Object>();
		String[] tableNames = new String[] {"t_acade_achive_info","t_patent_info","t_proj_achieve_info","t_stand_info","t_tec_innov_info","t_tec_man_innov","t_tec_standard_info"};

		sb.append(" from ")
		.append(" (SELECT * FROM (")
		.append(" SELECT distinct id,primitive_id,create_time,update_time")
		.append(" FROM ")
		.append(tableName)
		.append(" ORDER BY create_time DESC,update_time DESC) m")
		.append(" GROUP BY m.primitive_id")
		.append(" ORDER BY m.create_time DESC,m.update_time DESC) temp")
		.append(" LEFT JOIN ")
		.append(tableName)
		.append("  t ON t.id=temp.id");

		if(Arrays.asList(tableNames).contains(tableName.toLowerCase())) {//判断是否多表
			sb.append(" left join t_part_rank pr on t.id=pr.item_id");
		}

		sb.append(" where t.DEL_SIGN=0");

		if(Arrays.asList(tableNames).contains(tableName.toLowerCase())) {//判断是否多表
			sb.append(" and pr.erp_no = ? ");//进度查看只能查看自己作为参与人的
			sb.append(" and t.creater_id = ? ");//且自己是创建人的
			paramList.add(process.getDealErpNo());
		}else {//非多表
			sb.append(" and t.erp_no = ? ");
		}
    	paramList.add(process.getDealErpNo());

		if((null != process.getFlowStatus())&&(-1 != process.getFlowStatus())) {
			sb.append(" and t.flow_status=?");
			paramList.add(process.getFlowStatus());
		}
		if(null != process.getStartTime()){
        	sb.append(" and t.create_time >= ? ");
        	paramList.add(DateUtils.getStartTimeOfDay(process.getStartTime()).getTime());
        }
        if(null != process.getEndTime()){
        	sb.append(" and t.create_time <= ? ");
        	paramList.add(DateUtils.getEndTimeOfDay(process.getEndTime()).getTime());
        }

		return this.findUnique(WorkFlowCount.class, sb.toString(), paramList.toArray());
	}

	/**
	 * 获取字典列表
	 * @param value
	 * @return List<TSysDictionaryInfo>
	 *  */
	@Override
	public List<TSysDictionaryInfo> getDictionarys(String value){
		String sql = "select id,name,value from t_sys_dictionary_info ";
    	StringBuilder sb = new StringBuilder(sql);
    	sb.append(" where del_sign=0")
    	.append("  and p_id=(select id")
    	.append("  from t_sys_dictionary_info")
    	.append(" where del_sign=0")
    	.append(" and value=?)");

    	return this.findAllBySQL(TSysDictionaryInfo.class, sb.toString(), null, false, new Object[] {value});
	}

	/**
	 * 删除业务
	 * @param user 统一权限用户
	 * @param process
	 *  */
	@Override
	public void deleteBusiness(User user,ProcessListVo process) {
		String sql = "UPDATE ";
		StringBuilder sb = new StringBuilder(sql);


		sb.append(process.getTabName())
		.append(" set ")
		.append(" del_sign=1,")
		.append(" updater_id=#{updater_id,jdbcType=VARCHAR},")
		.append(" updater_name=#{updater_name,jdbcType=VARCHAR},")
		.append(" update_time=#{update_time}");
		sb.append(" where id=#{id,jdbcType=VARCHAR} ");

		Map<String, Object> params = new HashMap<String, Object>();
		params.put("updater_id", user.getFax());
		params.put("updater_name", user.getName());
		params.put("update_time", new Date());
		params.put("id", process.getBusinessId());

		this.executeSql(sb.toString(), params);
	}


	/**
	 * 查询待办数据
	 * 	 * @param page
	 * @param dto
	 */
	@Override
	public  void selectWaitApproveWorkFlow(Page<ApproveWorkFlowDto> page, ApproveWorkFlowDto dto){
		ArrayList<Object> arrayList = new ArrayList<Object>();
		String sql = "select wfa.work_flow_id,wfa.business_id,wfa.id as approve_id ,bwab.user_name,bwab.erp_no,bwab.handle_time," +
				"bwab.deal_time,bwab.business_type_id ,bwab.dept_id";
		StringBuilder sqlBuilder = new StringBuilder(sql);
		sqlBuilder.append(" from t_work_flow_info info ");
		sqlBuilder.append(" JOIN t_work_flow_approve wfa on info.sys_code='human_manage' and info.id=wfa.work_flow_id ");
		sqlBuilder.append(" JOIN t_work_flow_node_approve wfna on wfna.APPROVE_ID=wfa.id and wfna.APPROVER = ? and wfna.APPROVE_STATUS=0  ");
		arrayList.add(dto.getIscUserId());
		sqlBuilder.append(" LEFT JOIN  t_business_workflow_approve_bind bwab on bwab.approve_id = wfa.id ");
		sqlBuilder.append(" where 1=1 ");
		if(StringUtils.isNotEmpty(dto.getUserName())) {
			sqlBuilder.append(" and bwab.user_name like concat('%',?,'%') ESCAPE '/' ");
			arrayList.add(dto.getUserName());
		}
		if(StringUtils.isNotEmpty(dto.getErpNo())) {
			sqlBuilder.append(" and bwab.erp_no = ? ");
			arrayList.add(dto.getErpNo());
		}
		if(StringUtils.isNotEmpty(dto.getMouldId())) {
			sqlBuilder.append(" and bwab.business_type_id = ? ");
			arrayList.add(dto.getMouldId());
		}
		if(StringUtils.isNotEmpty(dto.getDeptId())) {
			sqlBuilder.append(" and bwab.dept_id = ? ");
			arrayList.add(dto.getDeptId());
		}
		if(null != dto.getStartTime()){
			sqlBuilder.append(" and bwab.deal_time >= ? ");
			arrayList.add(dto.getStartTime());
		}
		if(null != dto.getEndTime()){
			sqlBuilder.append(" and bwab.deal_time <= ? ");
			arrayList.add(dto.getEndTime());
		}
		sqlBuilder.append(" ORDER BY bwab.deal_time desc  ");
		this.findPageBySql(ApproveWorkFlowDto.class, sqlBuilder.toString(), page, arrayList.toArray());

	}

	/**
	 * 查询已办数据
	 * @param page
	 * @param dto
	 */
	@Override
	public  void selectDownApproveWorkFlow(Page<ApproveWorkFlowDto> page, ApproveWorkFlowDto dto){
		ArrayList<Object> arrayList = new ArrayList<Object>();
		String sql = "select wfa.work_flow_id,wfa.business_id,wfa.id as approve_id ,bwab.user_name,bwab.erp_no,bwab.handle_time," +
				"bwab.deal_time,bwab.business_type_id ,bwab.dept_id";
		StringBuilder sqlBuilder = new StringBuilder(sql);
		sqlBuilder.append(" from t_work_flow_info info ");
		sqlBuilder.append(" JOIN t_work_flow_approve wfa on info.sys_code='human_manage' and info.id=wfa.work_flow_id ");
		sqlBuilder.append(" JOIN t_work_flow_node_approve wfna on wfna.APPROVE_ID=wfa.id and wfna.APPROVER = ? and wfna.APPROVE_STATUS != 0  ");
		arrayList.add(dto.getIscUserId());
		sqlBuilder.append(" LEFT JOIN  t_business_workflow_approve_bind bwab on bwab.approve_id = wfa.id ");
		sqlBuilder.append(" where 1=1 ");
		if(StringUtils.isNotEmpty(dto.getUserName())) {
			sqlBuilder.append(" and bwab.user_name like concat('%',?,'%') ESCAPE '/' ");
			arrayList.add(dto.getUserName());
		}
		if(StringUtils.isNotEmpty(dto.getErpNo())) {
			sqlBuilder.append(" and bwab.erp_no = ? ");
			arrayList.add(dto.getErpNo());
		}
		if(StringUtils.isNotEmpty(dto.getMouldId())) {
			sqlBuilder.append(" and bwab.business_type_id = ? ");
			arrayList.add(dto.getMouldId());
		}
		if(dto.getFlowStatus()!=null) {
			sqlBuilder.append(" and bwab.flow_status = ? ");
			arrayList.add(dto.getFlowStatus());
		}
		if(StringUtils.isNotEmpty(dto.getDeptId())) {
			sqlBuilder.append(" and bwab.dept_id = ? ");
			arrayList.add(dto.getDeptId());
		}
		if(null != dto.getStartTime()){
			sqlBuilder.append(" and bwab.handle_time >= ? ");
			arrayList.add(dto.getStartTime());
		}
		if(null != dto.getEndTime()){
				sqlBuilder.append(" and bwab.handle_time <= ? ");
			arrayList.add(dto.getEndTime());
		}
		sqlBuilder.append(" ORDER BY bwab.handle_time desc  ");
		this.findPageBySql(ApproveWorkFlowDto.class, sqlBuilder.toString(), page, arrayList.toArray());

	}



	/**
	 * 跨库查询待办数据
	 * @param dto
	 * @return
	 * @autor swc
	 */
	@Override
	public  void findWaitAndDownApproveWorkFlowCrossDB(Page<ApproveWorkFlowDto> page, ApproveWorkFlowDto dto,List<TMouldInfo> moduleInfoList,String status,User user) {

		String sql = "SELECT work_flow_id, business_id, approve_id, user_name, temp.erp_no, primitive_id, handle_time, deal_time, flow_status," +
				" dic.name flow_status_name, mould_name, tab_name, tod.id dept_id,tod.name dept_name " ;
		StringBuilder sqlBuilder = new StringBuilder(sql);
		ArrayList<Object> arrayList = new ArrayList<Object>();

		sqlBuilder.append(" from ( ");
		for (int i=0 ;i<moduleInfoList.size(); i++){
			sqlq(sqlBuilder,arrayList,dto.getIscUserId(),moduleInfoList.get(i),status,user.getBaseOrgId());
			if(i<moduleInfoList.size()-1){
				sqlBuilder.append(" UNION ALL ");
			}
		}
		sqlBuilder.append(") temp " +
				"left JOIN t_dept_emp_rela tder on temp.erp_no=tder.erp_no " +
				"left join t_org_dept tod on tder.dept_id=tod.id " +
				"left join (select name,value from t_sys_dictionary_info where del_sign=0 and p_id=(select id  from t_sys_dictionary_info where del_sign=0 and value='FlowAuditStatus')) dic on dic.value = temp.flow_status ");

		sqlBuilder.append(" where 1=1 ");

		if(StringUtils.isNotEmpty(dto.getUserName())) {
			sqlBuilder.append(" and temp.user_name like concat('%',?,'%') ESCAPE '/' ");
			arrayList.add(dto.getUserName());
		}
		if(StringUtils.isNotEmpty(dto.getErpNo())) {
			sqlBuilder.append(" and temp.erp_no = ? ");
			arrayList.add(dto.getErpNo());
		}
		if(StringUtils.isNotEmpty(dto.getDeptId())) {
			sqlBuilder.append(" and tod.id = ? ");
			arrayList.add(dto.getDeptId());
		}
		if(null != dto.getStartTime()){
			if(status.equals(waitStatus)){
				sqlBuilder.append(" and temp.deal_time >= ? ");
			}else{
				sqlBuilder.append(" and temp.handle_time >= ? ");
			}
			arrayList.add(dto.getStartTime());
		}
		if(null != dto.getEndTime()){
			if(status.equals(waitStatus)){
				sqlBuilder.append(" and temp.deal_time <= ? ");
			}else{
				sqlBuilder.append(" and temp.handle_time <= ? ");
			}
			arrayList.add(dto.getEndTime());
		}
		if(dto.getFlowStatus()!=null) {
			sqlBuilder.append(" and temp.flow_status = ? ");
			arrayList.add(dto.getFlowStatus());
		}
		if(status.equals(waitStatus)){
			sqlBuilder.append(" ORDER BY deal_time desc  ");
		}else{
			sqlBuilder.append(" ORDER BY handle_time desc  ");
		}
		this.findPageBySql(ApproveWorkFlowDto.class, sqlBuilder.toString(), page, arrayList.toArray());
	}



	/**
	 * sql
	 * @param sqlBuilder
	 * @param arrayList
	 * @param approver
	 * @param table
	 */
	public void sqlq(StringBuilder sqlBuilder ,ArrayList<Object> arrayList ,String approver,TMouldInfo mouldInfo,String status ,String orgId){
		//测试环境与生产环境数据库名不一致
		if("test".equals(spring_profiles_active)){
			sqlBuilder.append("SELECT " +
					"flow.work_flow_id , flow.business_id ,flow.approve_id , " +
					"tab.creater_name user_name,tab.creater_id erp_no,tab.primitive_id primitive_id,tab.update_time handle_time,tab.CREATE_TIME deal_time,tab.flow_status, " +
					"mi.MOULD_NAME,mi.TAB_NAME ,mi.id mould_id " +
					"from(select wfa.work_flow_id,wfa.business_id,wfa.id as approve_id ,wfa.arrive_time " +
					"from `common-db`.t_work_flow_info info " +
					"join `common-db`.t_work_flow_approve wfa on info.sys_code='human_manage' and info.id=wfa.work_flow_id " +
					"join `common-db`.t_work_flow_node_approve wfna on wfna.APPROVE_ID=wfa.id and wfna.APPROVE_STATUS" +
					status +"0 " +
					"and wfna.APPROVER = ? ) flow  join t_business_workflow_bind bwb on flow.work_flow_id = bwb.flow_id and org_id = ? " +
					" JOIN t_mould_info mi on mi.ID = bwb.business_type_id AND mi.id = ? " +
					"JOIN ").append(mouldInfo.getTabName()).append(" tab on tab.id = flow.business_id ");
		} else{
			sqlBuilder.append("SELECT " +
					"flow.work_flow_id , flow.business_id ,flow.approve_id , " +
					"tab.creater_name user_name,tab.creater_id erp_no,tab.primitive_id primitive_id,tab.update_time handle_time,tab.CREATE_TIME deal_time,tab.flow_status, " +
					"mi.MOULD_NAME,mi.TAB_NAME ,mi.id mould_id " +
					"from(select wfa.work_flow_id,wfa.business_id,wfa.id as approve_id ,wfa.arrive_time " +
					"from `ixt-common-db`.t_work_flow_info info " +
					"join `ixt-common-db`.t_work_flow_approve wfa on info.sys_code='human_manage' and info.id=wfa.work_flow_id " +
					"join `ixt-common-db`.t_work_flow_node_approve wfna on wfna.APPROVE_ID=wfa.id and wfna.APPROVE_STATUS" +
					status +"0 " +
					"and wfna.APPROVER = ? ) flow  join t_business_workflow_bind bwb on flow.work_flow_id = bwb.flow_id and org_id = ? " +
					" JOIN t_mould_info mi on mi.ID = bwb.business_type_id  AND mi.id = ? " +
					"JOIN ").append(mouldInfo.getTabName()).append(" tab on tab.id = flow.business_id ");

//去重数据，
//			sqlBuilder.append("SELECT " +
//					"flow.work_flow_id , flow.business_id ,flow.approve_id , " +
//					"tab.creater_name user_name,tab.creater_id erp_no,tab.primitive_id primitive_id,tab.update_time handle_time,tab.CREATE_TIME deal_time,tab.flow_status, " +
//					"mi.MOULD_NAME,mi.TAB_NAME ,mi.id mould_id " +
//					"from ( " +
//					"SELECT * from ( " +
//					"select wfa.work_flow_id,wfa.business_id,wfa.id as approve_id ,wfa.arrive_time " +
//					"from `ixt-common-db`.t_work_flow_info info " +
//					"join `ixt-common-db`.t_work_flow_approve wfa on info.sys_code='human_manage' and info.id=wfa.work_flow_id " +
//					"join `ixt-common-db`.t_work_flow_node_approve wfna on wfna.APPROVE_ID=wfa.id and wfna.APPROVE_STATUS" +
//					status +"0 and wfna.APPROVER = ? ORDER BY wfa.arrive_time DESC) t GROUP BY business_id " +
//					") flow  join t_business_workflow_bind bwb on flow.work_flow_id = bwb.flow_id and org_id = ? " +
//					" JOIN t_mould_info mi on mi.ID = bwb.business_type_id AND mi.id = ? " +
//					"JOIN ").append(mouldInfo.getTabName()).append(" tab on tab.id = flow.business_id ");
		}
		 arrayList.add(approver);
		 arrayList.add(orgId);
		 arrayList.add(mouldInfo.getId());
	}

	@Override
	public List<ApproveWorkFlowDto> getBusinessAllData(String tableName,String flowId,String orgId) {
		String sql = "select distinct t.ID as BUSINESS_ID,t.CREATE_TIME as DEAL_TIME,t.creater_id as erp_no,t.creater_name as user_name," +
				"t.update_time as HANDLE_TIME,t.flow_status,t.primitive_id,t.orignal_id,wfa.id approve_id";
		StringBuilder sb = new StringBuilder(sql);
		sb.append(" from ")
				.append(tableName)
				.append(" t JOIN t_work_flow_approve wfa on t.id = wfa.business_id and wfa.work_flow_id = ? ")
				.append(" where 1=1")
				.append(" and t.org_id = ? ")
				.append(" and DEL_SIGN=0");
		return  this.findAllBySQL(ApproveWorkFlowDto.class, sb.toString(), null, false, new Object[] {flowId,orgId});
	}

	@Override
	public void getAllBusinessWorkflowBind(Page<TBusinessWorkflowBindVO> page, TBusinessWorkflowBindVO vo) {
		String sql = "select tbwb.id,tbwb.org_id ,tod.name org_name,tbwb.business_type_id,tmi.MOULD_NAME,tbwb.flow_id ,twfi.work_flow_name ";
		StringBuilder sb = new StringBuilder(sql);
		sb.append(" from t_business_workflow_bind tbwb ");
		sb.append(" left JOIN  t_org_dept tod on tod.id=tbwb.org_id ");
		sb.append(" left join t_work_flow_info twfi on twfi.id = tbwb.flow_id ");
		sb.append(" left join t_mould_info tmi on tmi.id = tbwb.business_type_id ");
		ArrayList<Object> arrayList = new ArrayList<Object>();
		sb.append(" where 1=1 ");
		if(StringUtils.isNotEmpty(vo.getOrgId())){
			sb.append(" and tbwb.org_id=? ");
			arrayList.add(vo.getOrgId());
		}
		if(StringUtils.isNotEmpty(vo.getBusinessTypeId())){
			sb.append(" and tbwb.business_type_id=? ");
			arrayList.add(vo.getBusinessTypeId());
		}
		if(StringUtils.isNotEmpty(vo.getFlowId())){
			sb.append(" and tbwb.flow_id=? ");
			arrayList.add(vo.getFlowId());
		}
		 this.findPageBySql(TBusinessWorkflowBindVO.class, sb.toString(), page, arrayList.toArray());
	}
}
